package com.neuedu.javase.jdbc.dao;

import com.neuedu.javase.jdbc.entity.Role;
import com.neuedu.javase.jdbc.entity.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @auth 金山老师
 * title:   书籍代码
 * creatime: 2022-10-12 11:11
 * 描述 :
 */
public class RoleDao extends BaseDao {

    public Role selectById(Integer id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "select * from role where role_id = ?";
        Role role = null;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);
            //绑定变量
            stmt.setInt(1,id);

            //执行sql
            rs = stmt.executeQuery();
            if(rs.next()){
                role = new Role(
                        rs.getInt("role_id"),
                        rs.getString("role_name"),
                        rs.getInt("active"),
                        rs.getTimestamp("createtime") );
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            super.close(conn,stmt, rs);
        }

        return role;
    }


    /**
     * 查询集合
     * @param condation
     * @param params
     * @return
     */
    public List<Role> selectList(String condation ,Object ... params){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "select * from role  " +(condation!=null?condation:"");
        List list = new ArrayList();
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);


            //绑定变量
            int index = 0;
            for (Object param : params) {
                stmt.setObject(++index,param);
            }

            //执行sql
            rs = stmt.executeQuery();
            while(rs.next()){
              Role role = new Role(
                      rs.getInt("role_id"),
                      rs.getString("role_name"),
                      rs.getInt("active"),
                      rs.getTimestamp("createtime") );

                list.add(role);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            super.close(conn,stmt, rs);
        }

        return list;
    }


    public List<Role> selectList(String condation ,int current,int size,Object ... params){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int start  = (current-1)*size;
        String sql = "SELECT * FROM (  select * from role  " +(condation!=null?condation:"")+"  )  t LIMIT "+start+" ,"+size+"   " ;
        List list = new ArrayList();
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);


            //绑定变量
            int index = 0;
            for (Object param : params) {
                stmt.setObject(++index,param);
            }

            //执行sql
            rs = stmt.executeQuery();
            while(rs.next()){
                Role role = new Role(
                        rs.getInt("role_id"),
                        rs.getString("role_name"),
                        rs.getInt("active"),
                        rs.getTimestamp("createtime") );

                list.add(role);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            super.close(conn,stmt, rs);
        }

        return list;
    }




    public long selectCount(String condation ,Object ... params){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String sql = "SELECT count(1) count FROM (  select * from role  " +(condation!=null?condation:"")+"  )  t    " ;
        long count = 0;
        try {
            conn = getConnection();
            stmt = conn.prepareStatement(sql);


            //绑定变量
            int index = 0;
            for (Object param : params) {
                stmt.setObject(++index,param);
            }

            //执行sql
            rs = stmt.executeQuery();
            if(rs.next()){
               count  = rs.getLong("count");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            super.close(conn,stmt, rs);
        }

        return count;
    }



    public int insert(String sql, Object ... params) {
        Connection con = null;
        PreparedStatement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();
            stmt = con.prepareStatement(sql);

            System.out.println("sql = " + sql);

            //绑定变量
            int index = 0;
            for (Object param : params) {
                stmt.setObject(++index ,param );
            }
            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }


    /**
     * 更新
     * @param sql
     * @param params
     * @return
     */
    public int update(String sql, Object ... params) {
        Connection con = null;
        PreparedStatement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();
            stmt = con.prepareStatement(sql);

            System.out.println("sql = " + sql);

            //绑定变量
            int index = 0;
            for (Object param : params) {
                stmt.setObject(++index ,param );
            }
            count = stmt.executeUpdate();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }


    /**
     * 根据主键删除
     * @param id
     * @return
     */
    public int deleteById(int id) {
        Connection con = null;
        PreparedStatement stmt = null;
        int count = 0;
        try {

            con = super.getConnection();
            String sql = "delete from role  where role_id = ?";
            stmt = con.prepareStatement(sql);
            System.out.println("sql = " + sql);

            //绑定变量
             stmt.setObject(1 ,id );
            count = stmt.executeUpdate();  //updte insert delete

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            super.close(con, stmt);
        }

        return count;
    }



}
